/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package compiler;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/**
 *
 * @author Paul.Kogel
 */

public class Main {

    private static List<Table> tables;
    private static List<Relation> relations;
    private static String inputFile = "";
    private static String outputFile = "";
    private static String dbName = "";
    //contains input file; each item represent another line
    private static List<String> inptFileLines;

    public static enum Mode {

        INFO, HELP, COMPILE, CHECK, DECOMPILE
    };
    //determines in which mode the app was started
    private static Mode Mode;

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

        /*TODO remove commented block later...    
        Table t = new Table();
        t.getColumns().get(0).setDatatype(Column.Datatypes.DOUBLE);        
        Relation r = new Relation();
        r.setOperation(Relation.Operations.UPDATE);
         * 
         */

        //set default mode:
        Mode = Mode.HELP;

        //append an empty line so that output looks better:
        System.out.append("\n");

        //check commandline args and set mode based on these:
        if (args.length > 0) {
            String type = args[0];
            if (type.equals("-i")) {
                Mode = Mode.INFO;
            }
            if (type.equals("-h")) {
                Mode = Mode.HELP;
            }

            if (type.equals("-c") || type.equals("-t")) {
                if (args.length >= 2) {
                    if (new File(args[1]).exists()) {
                        inputFile = args[1];

                        if (type.equals("-c")) {
                            if (args.length >= 3) {
                                //TODO check output file...
                                outputFile = args[2];

                                //check if input file is sql or easy-sql-File:                                
                                if (inputFile.substring(inputFile.lastIndexOf(".") + 1).toLowerCase().equals("sql")) {
                                    Mode = Mode.DECOMPILE;
                                } else {
                                    Mode = Mode.COMPILE;
                                }
                            } else {
                                System.out.append("Error in commandline: To compile you need to enter the inout file and the output file.\n");
                                System.exit(0);
                            }
                        } else {
                            Mode = Mode.CHECK;
                        }
                    } else {
                        System.out.append("Error in commandline: Input file does not exist.\n");
                        System.exit(0);
                    }
                } else {
                    System.out.append("Error in commandline: To check you need to enter the input file.\n");
                    System.exit(0);
                }


            }
        }

        //perform operations based on mode:
        if (Mode == Mode.INFO) {
            System.out.append("Datapace compiler\n");
            System.out.append("Version 1.0\n");
            System.out.append("(c) 2011\n");
        }
        if (Mode == Mode.HELP) {
            System.out.append("Available commands:\n");
            System.out.append("Compile source file: -c <input file> <output file> \n");
            System.out.append("Check source file for errors: -t <input file> \n");
            System.out.append("Get info on compiler: -i\n");
            System.out.append("Get overview of available commands: -h\n\n");
            System.out.append("Note: files must be specified with its full path.\n");
        }
        if ((Mode == Mode.COMPILE) || (Mode == Mode.CHECK) || (Mode == Mode.DECOMPILE)) {
            readInputFile();
        }
    }

    public static void readInputFile() {

        inptFileLines = new ArrayList();

        //contains whole content of sql-file in DECOMPILE-mode:
        String content = "";

        //Reads the input file and adds each line as new item in inptFileLines
        BufferedReader in = null;

        try {
            String line = null;
            in = new BufferedReader(new InputStreamReader(new FileInputStream(inputFile)));
            do {
                line = in.readLine();
                if (line != null) {                    
                    line = line.trim();
                    if (line.length() > 0) {
                        if (Mode == Mode.COMPILE || Mode == Mode.CHECK) {
                            inptFileLines.add(line);
                        } else {
                            //kill comments:
                            if (!line.startsWith("--")) {
                                if (line.contains("`")) {
                                    line = line.replace("`", "'");
                                }
                                if (line.contains("´")) {
                                    line = line.replace("´", "'");
                                }

                                content += line;
                            }
                        }
                    }
                }

            } while (line != null);

            in.close();

            if (Mode == Mode.DECOMPILE) {
                if (content.length() > 0) {
                    inptFileLines = Arrays.asList(content.split(";"));
                    performSteps4and5();
                } else {
                    System.out.append("Error in commandline: Input file is empty.\n");
                    System.exit(0);
                }
            } else {
                performSteps1to3();
            }

        } catch (FileNotFoundException err) {
            System.out.append("Error in commandline: Input file does not exist.\n");
            System.exit(0);
        } catch (Exception err) {
            System.out.append(err + "\nCritical error: program returned an undefined issue.\n");
            System.exit(0);
        }
    }

    public static void performSteps1to3() {

        //Add parser to enable user not being forced to write every keyword into new line

        String[] lines = new String[inptFileLines.size()];
        int p = 0;

        while (p < inptFileLines.size()) {
            lines[p] = inptFileLines.get(p);
            p++;
        }
        p = 0;

        List<String> rows = new ArrayList();
        
        for (String row : lines) {
            if (!row.equals("{") && !row.equals("}") && row.contains("{")) {               
                String[] split = row.split("\\{");
                int pp = 0;
                for (String l : split) {
                    pp++;
                    if(l.equals("") && pp==1) {}
                    else {
                        rows.add(l);
                    }
                    if (pp < split.length) {                       
                        rows.add("{");                       
                    }
                }
            }
            else {
                rows.add(row);
            }
        }

        lines = new String[rows.size()];
        for(String x : rows) {
            lines[p]=x;
            p++;
        }
        p=0;
        
        rows.clear();       
        
        for (String row : lines) {
            if (!row.equals("}") && row.contains("}")) {              
                String[] split = row.split("\\}");
                int pp = 0;
                for (String l : split) {
                    pp++;
                    rows.add(l);
                    if (pp < split.length) {
                        rows.add("}");
                    }
                }
                if(row.endsWith("}")) {
                    rows.add("}");
                }
            }
            else {
                rows.add(row);
            }
        }

        lines = new String[rows.size()];
        for(String x : rows) {
            lines[p]=x;
            p++;
        }
        p = 0;

        for(String b:lines){
            if(b.equals("")){
                rows.remove(p);
            }
            p++;
        }
        p = 0;
        
        inptFileLines.clear();
        for(String y : rows) {
            p++;
            inptFileLines.add(y);
        }
        p=0;

        //Check for 'database' keyword at beginning; remove it

        if (inptFileLines.get(0).trim().length() > 0 && (!inptFileLines.get(0).equals("{"))) {
            String _dbName = inptFileLines.get(0);

            if (_dbName.equals("database")) {
                dbName = "";
            } else {
                dbName = _dbName;
            }
            inptFileLines.remove(0);
        } else {
            System.out.append("Syntax error in line 1: missing keyword\n");
            System.exit(0);
        }


        //Check if all brackets are set correctly (+ database.relations check)
        if (inptFileLines.get(0).equals("{")) {
            Integer opened = 0;
            Integer pos = 0;
            Integer relate = 0;
            Integer related = 0;
            String last = "database";

            while (pos < inptFileLines.size()) {

                if (inptFileLines.get(pos).equals("{")) {
                    if (last.equals("}") || last.equals("{")) {
                        System.out.append("Syntax error in line "+Integer.toString(pos+2)+": Missing keyword.\n");
                        System.exit(0);
                    } else {
                        opened++;
                    }
                }
                if (inptFileLines.get(pos).equals("}")) {
                    if (last.equals("{") && !(relate==1)) {
                        System.out.append("Syntax error in line "+Integer.toString(pos+2)+": Empty entry.\n");
                        System.exit(0);
                    } else {
                        opened--;
                    }
                    if(relate==1 && opened==1){
                        relate=0;
                    }
                }

                if (!inptFileLines.get(pos).equals("{") && !inptFileLines.get(pos).equals("}")) {

                    if(inptFileLines.get(pos).equals("database.relations")) {
                        relate++;
                        related++;
                        if(related>1) {
                            System.out.append("Sytax error in line "+Integer.toString(pos+2)+": 'database.relations' included more than once.");
                            System.exit(0);
                        }
                    }

                    if (!last.equals("{") && !last.equals("}") && (relate==0 || !(relate==1 && opened==2))) {
                        System.out.append("Syntax error in line "+Integer.toString(pos+2)+": Missing bracket.\n");
                        System.exit(0);
                    }

                    if (opened==1) {
                        if(last.equals("{") || (last.equals("}") && inptFileLines.get(pos-2).equals("}") && inptFileLines.get(pos-3).equals("}"))) {
                        } else {
                                System.out.append("Syntax error around line "+Integer.toString(pos+1)+": Missing bracket.\n");
                                System.exit(0);     
                        }
                    }

                    if (opened==2 && relate!=1) {
                        if(last.equals("{") || (last.equals("}") && inptFileLines.get(pos-2).equals("}"))) {
                        } else {
                                System.out.append("Syntax error around line "+Integer.toString(pos+1)+": Missing bracket.\n");
                                System.exit(0);                           
                        }
                    }

                    if (opened==3) {
                        if(last.equals("{") || (last.equals("}"))) {
                        } else {
                                System.out.append("Syntax error around line "+Integer.toString(pos+1)+": Missing bracket.\n");
                                System.exit(0);                           
                        }
                    }
                }

                if (opened > 4) {
                    System.out.append("Syntax error in line "+Integer.toString(pos+2)+": More than 4 layers at once or missing closing bracket\n");
                    System.exit(0);
                }

                if (opened < 0) {
                    System.out.append("Syntax error in undefinable line (before line "+Integer.toString(pos+2)+"): missing opening bracket\n");
                    System.exit(0);
                }

                last = inptFileLines.get(pos);

                pos++;
            }

            if (opened > 0) {
                System.out.append("Syntax error in undefinable line: missing closing bracket\n");
                System.exit(0);
            }

        } else {
            System.out.append("Syntax error in line 2: missing opening bracket\n");
            System.exit(0);
        }

        performSteps4and5();
    }    
    
    public static void performSteps4and5() {
        //parse pre-sorted list and create Table- and Relation-objects based on the results

        tables = new ArrayList();
        relations = new ArrayList();


        if (Mode == Mode.DECOMPILE) {
            //parse input sql-file:
            try {
                for (String s : inptFileLines) {
                    //check for keywords:
                    if (s.toLowerCase().startsWith("create table")) {
                        String prefix = s.substring(0, s.indexOf("("));
                        String inner = s.substring(s.indexOf("(") + 1, s.lastIndexOf(")"));

                        Table t = new Table();
                        t.setName(prefix.substring(prefix.indexOf("'") + 1, prefix.lastIndexOf("'")));

                        String[] innerContent = inner.split(",");
                        for (String sI : innerContent) {
                            if (sI.toLowerCase().startsWith("'")) {
                                //add new column:
                                Column c = new Column();
                                c.setName(sI.substring(sI.indexOf("'") + 1, sI.lastIndexOf("'")));
                                t.getColumns().add(c);
                            }
                        }

                        tables.add(t);
                    }

                    if (s.toLowerCase().startsWith("insert into")) {
                        String prefix = s.substring(0, s.indexOf("("));

                        List<String> columns = Arrays.asList(s.substring(s.indexOf("(") + 1, s.indexOf(")")).replace("'", "").split(","));
                        for (int i = 0; i < columns.size(); i++) {
                            columns.set(i, columns.get(i).trim());
                        }

                        String values = s.substring(s.toLowerCase().indexOf("values") + 6);

                        List<Column> preColumns = new ArrayList();

                        //add columns to pre-columns:
                        for (String co : columns) {
                            Column c = new Column();
                            c.setName(co);
                            preColumns.add(c);
                        }

                        //add values to pre-columns:
                        while (values.length() > 0) {

                            String set = values.substring(values.indexOf("(") + 1, values.indexOf(")"));
                            String[] subVals = set.split(",");

                            for (int i = 0; i < subVals.length; i++) {
                                preColumns.get(i).getValues().add(subVals[i].replace("'", "").trim());
                            }

                            values = values.substring(values.indexOf(")") + 1);
                        }

                        String tName = prefix.substring(prefix.indexOf("'") + 1, prefix.lastIndexOf("'"));


                        for (Table t : tables) {
                            //get target table:
                            if (t.getName().toLowerCase().equals(tName.toLowerCase())) {
                                //get fitting column with values:
                                for (Column preC : preColumns) {
                                    //get columns where values should be added:
                                    for (Column c : t.getColumns()) {
                                        if (preC.getName().toLowerCase().equals(c.getName().toLowerCase())) {
                                            c.setValues(preC.getValues());
                                            break;
                                        }
                                    }
                                }
                                break;
                            }
                        }
                    }
                    if (s.toLowerCase().startsWith("alter table") && s.toLowerCase().contains("foreign key")) {
                        String prefix = s.substring(0, s.toLowerCase().indexOf("add"));
                        String suffix = s.substring(s.toLowerCase().indexOf("add") + 3);

                        String tForeign = prefix.substring(prefix.indexOf("'") + 1, prefix.lastIndexOf("'"));

                        String[] constraints = suffix.split(",");

                        for (String c : constraints) {
                            String key = c.substring(0, c.toLowerCase().indexOf("references"));
                            String suffix2 = c.substring(c.toLowerCase().indexOf("references") + 10);

                            //TODO: normally, this must not be the primary column as the key can be defined using a generic term when creating the table!
                            String cForeign = key.substring(key.indexOf("(") + 1, key.lastIndexOf(")")).replace("'", "").trim();

                            String tPrimary = suffix2.substring(0, suffix2.indexOf("("));
                            tPrimary = tPrimary.substring(tPrimary.indexOf("'") + 1, tPrimary.lastIndexOf("'"));

                            String cPrimary = suffix2.substring(suffix2.indexOf("(") + 1, suffix2.indexOf(")"));
                            cPrimary = cPrimary.substring(cPrimary.indexOf("'") + 1, cPrimary.lastIndexOf("'"));

                            String operation = suffix2.substring(suffix2.indexOf(")"));

                            //create relation:
                            Relation r = new Relation();

                            Table sT = new Table();
                            sT.setName(tPrimary);
                            Column sC = new Column();
                            sC.setName(cPrimary);

                            Table eT = new Table();
                            eT.setName(tForeign);
                            Column eC = new Column();
                            eC.setName(cForeign);

                            r.setStartT(sT);
                            r.setStartC(sC);
                            r.setEndT(eT);
                            r.setEndC(eC);

                            //check if both operations are defined:
                            if(operation.toLowerCase().contains("on update")&& operation.toLowerCase().contains("on delete"))
                            {
                                String updOp = "";
                                String delOp = "";
                                
                                int upd = operation.toLowerCase().indexOf("on update");
                                int del = operation.toLowerCase().indexOf("on delete");
                                
                                if(upd<del)
                                {
                                    updOp = operation.substring(upd + 10,del);
                                    delOp = operation.substring(del+10);
                                }
                                else
                                {
                                    delOp = operation.substring(del + 10,upd);                                    
                                    updOp = operation.substring(upd+10);                                    
                                }
                                
                                r.setUpdateOperation(Relation.stringToOp(updOp));
                                r.setDeleteOperation(Relation.stringToOp(delOp));
                            }
                            else
                            {
                                if (operation.toLowerCase().contains("on update")) 
                                {
                                    operation = operation.substring(operation.toLowerCase().indexOf("on update") + 10).toLowerCase();
                                    r.setUpdateOperation(Relation.stringToOp(operation));
                                }                           
                                if (operation.toLowerCase().contains("on delete")) 
                                {
                                    operation = operation.substring(operation.toLowerCase().indexOf("on delete") + 10).toLowerCase();
                                    r.setDeleteOperation(Relation.stringToOp(operation));
                                }                           
                            }
                            
                            relations.add(r);
                        }
                    }
                }
            } catch (Exception e) {
                System.out.append("Error compiling: Your syntax seems to be wrong.\n");
                System.exit(0);
            }
        } else {
            //remove initial and last bracket:
            inptFileLines.remove(0);
            inptFileLines.remove(inptFileLines.size() - 1);

            //set line counter (3 because database keyword and initial bracket have already been removed)
            int lineNumber = 3;

            //outer loop loops different layers:
            while (inptFileLines.size() > 0) {
                String current = inptFileLines.get(0);

                if (current.equals("database.relations")) {
                    // <editor-fold  defaultstate="collapsed" desc="add new relation to relations list">                

                    //remove relation line and open bracket:
                    inptFileLines.remove(0);
                    inptFileLines.remove(0);

                    lineNumber += 2;

                    //look for the bracket that closes this block:
                    int opened = 1;

                    int pos = 0;
                    while (opened > 0) {
                        if (inptFileLines.get(pos).equals("{")) {
                            opened++;
                        }
                        if (inptFileLines.get(pos).equals("}")) {
                            opened--;
                        }

                        pos++;
                    }
                    pos--;

                    //loop different relations:
                    int i = 0;
                    while (i < pos) {
                        String currentInner = inptFileLines.get(0);

                        if (currentInner.contains("->")) {

                            Relation r = new Relation();

                            String start = currentInner.substring(0, currentInner.indexOf("->"));
                            String end = currentInner.substring(currentInner.indexOf("->") + 2);

                            if (!start.contains(".")) {
                                System.out.append("Compiling error (line " + lineNumber + "): Missing separator (\".\") between table and column in primary key statement.\n");
                                System.exit(0);
                            }
                            if (!end.contains(".")) {
                                System.out.append("Compiling error (line " + lineNumber + "): Missing separator (\".\") between table and column in foreign key statement.\n");
                                System.exit(0);
                            }

                            // <editor-fold  defaultstate="collapsed" desc="create relation"> 
                            if (start.contains(".") && (end.contains("."))) {

                                String startTName = start.substring(0, start.indexOf(".")).trim();
                                String startCName = start.substring(start.indexOf(".") + 1).trim();


                                String endTName = end.substring(0, end.indexOf(".")).trim();
                                String endCName = end.substring(end.indexOf(".") + 1).trim();

                                //add attributes:                                                                                    
                                Table t = new Table();
                                t.setName(startTName);
                                r.setStartT(t);

                                t = new Table();
                                t.setName(endTName);
                                r.setEndT(t);

                                Column c = new Column();
                                c.setName(startCName);
                                r.setStartC(c);

                                c = new Column();
                                c.setName(endCName);
                                r.setEndC(c);


                                //HERE comes the inner block...                            

                                //remove relation statement:
                                inptFileLines.remove(0);

                                //check if operation block is included:
                                if (inptFileLines.get(0).equals("{")) {
                                    inptFileLines.remove(0);

                                    //TODO this is not working very well...
                                    lineNumber += 2;

                                    //look for the bracket that closes this block:
                                    int opened3 = 1;


                                    ///+++ operation & action +++
                                    int pos3 = 0;
                                    while (opened3 > 0) {
                                        if (inptFileLines.get(pos3).equals("{")) {
                                            opened3++;
                                        }
                                        if (inptFileLines.get(pos3).equals("}")) {
                                            opened3--;
                                        }

                                        pos3++;
                                    }
                                    pos3--;

                                    if (pos3 > 1) {

                                        //loop different operations for current relation:
                                        for (int x = 0; x < pos3; x += 4) {

                                            // <editor-fold  defaultstate="collapsed" desc="set action & operation"> 
                                            String action = inptFileLines.get(x).toLowerCase();

                                            if (action.equals("update") || action.equals("delete")) {
                                                //valid operation detected; now checking block...
                                                String op = inptFileLines.get(x + 2).toLowerCase();

                                                Relation.Operations o = null;

                                                if (!op.equals("}")) {
                                                    //check if operation is valid:
                                                    if (op.equals("restrict")) {
                                                        o = Relation.Operations.RESTRICT;
                                                    }
                                                    if (op.equals("cascade")) {
                                                        o = Relation.Operations.CASCADE;
                                                    }
                                                    if (op.equals("no action")) {
                                                        o = Relation.Operations.NO_ACTION;
                                                    }
                                                    if (op.equals("set null")) {
                                                        o = Relation.Operations.SET_NULL;
                                                    }

                                                    if (o == null) {
                                                        System.out.append("Syntax error in line " + (lineNumber+2) + ": Unknown operation type.");
                                                        System.exit(0);
                                                    } else {
                                                        //set operation:
                                                        if (action.equals("update")) {
                                                            r.setUpdateOperation(o);
                                                        }
                                                        if (action.equals("delete")) {
                                                            r.setDeleteOperation(o);
                                                        }

                                                        lineNumber += 5;

                                                    }
                                                } else {
                                                    System.out.append("Syntax error in line " + lineNumber + ": No operation specified.");
                                                    System.exit(0);
                                                }
                                            } else {
                                                System.out.append("Syntax error in line " + lineNumber + ": Unsupported action.");
                                                System.exit(0);
                                            }
                                            // </editor-fold>
                                        }


                                        //add relation to list:
                                        relations.add(r);

                                        //clear op/action block:
                                        for (int x = 0; x < pos3; x++) {
                                            inptFileLines.remove(0);
                                        }

                                        //remove closing brackte for op/action-block:
                                        inptFileLines.remove(0);


                                        //add lines for op/action-block and 3 for inner+outer bracket + rel.statement
                                        i += pos3 + 3;

                                    } else {
                                        System.out.append("Compiling error (line " + lineNumber + "): No operation specified.\n");
                                        System.exit(0);

                                    }
                                } else {
                                    //no operations provived:
                                    relations.add(r);
                                    i += 3;
                                }

                            }
                            // </editor-fold>

                        } else {
                            System.out.append("Compiling error (line " + lineNumber + "): Missing statement \"->\"\n");
                            System.exit(0);
                        }
                    }
                    //remove last bracket:
                    inptFileLines.remove(0);
                    lineNumber++;
                    // </editor-fold>
                } else {
                    if (current.equals("database.entries")) {
                        //remove relation line and open bracket:
                        inptFileLines.remove(0);
                        inptFileLines.remove(0);

                        lineNumber += 2;

                        //look for the bracket that closes this block:
                        int opened1 = 1;

                        int posOuter = 0;
                        while (opened1 > 0) {
                            if (inptFileLines.get(posOuter).equals("{")) {
                                opened1++;
                            }
                            if (inptFileLines.get(posOuter).equals("}")) {
                                opened1--;
                            }

                            posOuter++;
                        }
                        posOuter--;

                        //now loop this block:
                        int x = 0;
                        while (x < posOuter) {

                            String currentOuter = inptFileLines.get(0);

                            // <editor-fold  defaultstate="collapsed" desc="add new table to tables list">                                                                
                            if (currentOuter.equals("") || currentOuter.contains("->") || currentOuter.equals("{")) {
                                System.out.append("Syntax error (line " + lineNumber + "): Invalid or missing keyword.\n");
                                System.exit(0);
                            } else {
                                                                
                                
                                //check if table name already exists:
                                for (Table t : tables) {
                                    if (t.getName().equals(currentOuter)) {
                                        System.out.append("Compiling error (line " + lineNumber + "): " + currentOuter + " has been already defined.");
                                        System.exit(0);
                                    }
                                }

                                Table t = new Table();
                                t.setName(currentOuter);
                                
                                //check if table has content:
                                if((!inptFileLines.get(1).equals("{"))||(inptFileLines.get(2).equals("}")))
                                {
                                    //empty:
                                    System.out.append("Compiling error (line " + lineNumber + "): table '" + currentOuter + "' has no columns.");
                                    System.exit(0);
                                    /*
                                    if(!inptFileLines.get(1).equals("{"))
                                    {
                                        inptFileLines.remove(0);
                                        lineNumber ++;
                                        
                                        x+=1;
                                    }
                                    if(!inptFileLines.get(2).equals("{"))
                                    {
                                        inptFileLines.remove(0);
                                        inptFileLines.remove(0);
                                        inptFileLines.remove(0);
                                        lineNumber +=3;
                                        
                                        x+=3;
                                    }
                                                                        
                                    
                                    t.setColumns(new ArrayList<Column>());
                                     * 
                                     */
                                }
                                else
                                {
                                    // <editor-fold  defaultstate="collapsed" desc="get columns and values">        
                                
                                    //remove table name and following bracket:                                  
                                    inptFileLines.remove(0);
                                    inptFileLines.remove(0);
                                    lineNumber += 2;

                                    //check if block is given:                    
                                    //get position of closing bracket of this block:
                                    int opened = 1;

                                    int pos = 0;
                                    while (opened > 0) {
                                        if (inptFileLines.get(pos).equals("{")) {
                                            opened++;
                                        }
                                        if (inptFileLines.get(pos).equals("}")) {
                                            opened--;
                                        }

                                        pos++;
                                    }
                                    pos--; //i.e. now position of closing bracket

                                    if (pos > 0) {
                                        //loop inner block (columns):
                                        int i = 0;
                                        while (i < pos) {
                                            String currentInner = inptFileLines.get(i);

                                            if (currentInner.equals("") || currentInner.contains("->") || currentInner.equals("{")) {
                                                System.out.append("Compiling error (line " + lineNumber + "): Invalid or missing keyword.\n");
                                                System.exit(0);
                                            } else {
                                                //check if column name already exists:
                                                for (Column c : t.getColumns()) {
                                                    if (c.getName().equals(currentInner)) {
                                                        System.out.append("Compiling error (line " + Integer.toString(i + lineNumber) + "): " + currentInner + " has been already defined.\n");
                                                        System.exit(0);
                                                    }
                                                }


                                                //create new column, add values and add definings (if set):
                                                Column c = new Column();
                                                if(currentInner.contains("(") && currentInner.contains(")"))
                                                {
                                                String defines = currentInner.substring(currentInner.lastIndexOf("(")+1, currentInner.lastIndexOf(")"));
                                                currentInner = currentInner.substring(0, currentInner.lastIndexOf("("));
                                                String type = "";
                                                String length = "";
                                                if(defines.substring(0, 4).equals("type"))
                                                {
                                                    if(defines.contains(","))
                                                    {
                                                    type = defines.substring(defines.indexOf("=")+1, defines.indexOf(","));
                                                    }else{
                                                    type = defines.substring(defines.indexOf("=")+1, defines.length());
                                                    }
                                                }else{
                                                    if(defines.contains(","))
                                                    {
                                                    length = defines.substring(defines.indexOf("=")+1, defines.indexOf(","));
                                                    }else{
                                                    length = defines.substring(defines.indexOf("=")+1, defines.length());
                                                    }
                                                }
                                                if(defines.contains(","))
                                                {
                                                    if(defines.substring(defines.indexOf(",")+2, defines.indexOf(",")+6).equals("type"))
                                                    {
                                                        type = defines.substring(defines.indexOf(","), defines.length());
                                                        type = type.substring(type.indexOf("=")+1);
                                                    }else{
                                                        length = defines.substring(defines.indexOf(",")+1, defines.length());
                                                        length = length.substring(length.indexOf("=")+1);
                                                    }
                                                }
                                                
                                                if(!type.equals(""))
                                                {
                                                if(type.toLowerCase().equals("integer"))
                                                {
                                                    c.setDatatype(Column.Datatypes.INTEGER);
                                                }else if(type.toLowerCase().equals("string"))
                                                {
                                                    c.setDatatype(Column.Datatypes.VARCHAR);
                                                }else if(type.toLowerCase().equals("text"))
                                                {
                                                    c.setDatatype(Column.Datatypes.TEXT);
                                                }else if(type.toLowerCase().equals("date"))
                                                {
                                                    c.setDatatype(Column.Datatypes.DATE);
                                                }else if(type.toLowerCase().equals("time"))
                                                {
                                                    c.setDatatype(Column.Datatypes.TIME);
                                                }else if(type.toLowerCase().equals("double"))
                                                {
                                                    c.setDatatype(Column.Datatypes.DOUBLE);
                                                }else if(type.toLowerCase().equals("boolean"))
                                                {
                                                    c.setDatatype(Column.Datatypes.TINYINT);
                                                }else
                                                {
                                                    c.setDatatype(Column.Datatypes.UNKNOWN);
                                                    c.dtype = c.getDatatypeName(type);
                                                }
                                                }

                                                if(!length.equals(""))
                                                {
                                                c.setLength(Integer.parseInt(length));
                                                }
                                                
                                                }


                                                c.setName(currentInner);

                                                if (!((!inptFileLines.get(i + 1).equals("{")) || (inptFileLines.get(i + 2).equals("}")))) {
                                                    String next = inptFileLines.get(i + 2);

                                                    List<String> vals = new ArrayList();
                                                    Collections.addAll(vals, next.split(","));


                                                    //trim vals and set, if necessary, AI:
                                                    int max = vals.size();
                                                    for (int z = 0; z < max; z++) {
                                                        vals.set(z, vals.get(z).trim());

                                                        if (vals.get(z).equals("+1") && (z == vals.size() - 1)) {
                                                            vals.remove(z);
                                                            c.setIsAI(true);
                                                        }
                                                    }

                                                    c.setValues(vals);

                                                    //if first column in table, mark as primary key:
                                                    if (i == 0) {
                                                        c.setIsPrimaryKey(true);
                                                    }

                                                    t.getColumns().add(c);

                                                } else {
                                                    System.out.append("Compiling error (line " + Integer.toString(i + lineNumber) + "): Column '" + c.getName() + "' in table '" + t.getName() + "' is empty.\n");
                                                    System.exit(0);
                                                }
                                            }
                                            i += 4;
                                        }
                                        for (int z = 0; z <= pos; z++) {
                                            inptFileLines.remove(0);
                                        }
                                        lineNumber += (pos + 1);

                                        //check number of values:
                                        int defNumber = t.getColumns().get(0).getValues().size();
                                        for (Column c : t.getColumns()) {
                                            int cSize = c.getValues().size();
                                            if (cSize != defNumber) {
                                                System.out.append("Compiling error: Column '" + c.getName() + "' in table '" + t.getName() + "' has not the right amount of entries.\n");
                                                System.exit(0);
                                            }

                                        }
                                    } else {
                                        //remove closed bracket:
                                        inptFileLines.remove(0);
                                    }
                                    // </editor-fold>        

                                    //increase index var (add 3 to pos because of table name and brackets surrounding table):
                                    x += (pos + 3);
                                }
                                tables.add(t);                               
                            }
                            //</editor-fold>

                        }

                        //remove last bracket:
                        inptFileLines.remove(0);
                        lineNumber++;
                    } else {
                        //invalid keyword:
                        System.out.append("Compiling error (line " + lineNumber + "): Invalid or missing keyword.\n");
                        System.exit(0);
                    }
                }
            }



            //check relations:
            for (Relation r : relations) {
                Boolean matchesFirst = false;
                Boolean matchesSecond = false;


                //check whether tables exist:
                for (Table t : tables) {

                    //primary key:
                    if (t.getName().equals(r.getStartT().getName())) {
                        for (Column c : t.getColumns()) {

                            if (c.getName().equals(r.getStartC().getName())) {
                                r.setStartT(t);
                                r.setStartC(c);

                                c.setIsPrimaryKey(true);

                                //unset first primary key:
                                if (t.getColumns().get(0) != c) {
                                    t.getColumns().get(0).setIsPrimaryKey(false);
                                }

                                matchesFirst = true;

                                break;
                            }


                        }
                    }
                             
                    //foreign key:
                    if (t.getName().equals(r.getEndT().getName())) {
                        
                        for (Column c : t.getColumns()) {
                            
                            if (c.getName().equals(r.getEndC().getName())) {
                                
                                r.setEndT(t);
                                r.setEndC(c);

                                //if foreign key is already primary key, remove primary mark:
                                c.setIsPrimaryKey(false);

                                c.setIsForeignKey(true);
                                
                                matchesSecond = true;
                                break;
                            }
                        }
                    }
                }


                //show error messages if keys don´t exist:
                if (!(matchesFirst && matchesSecond)) {
                    if (!matchesFirst) {
                        System.out.append("Compiling error in relations section: Field '" + r.getStartT().getName() + "." + r.getStartC().getName() + "' does not exist.\n");
                    }
                    if (!matchesSecond) {
                        System.out.append("Compiling error in relations section: Field '" + r.getEndT().getName() + "." + r.getEndC().getName() + "' does not exist.\n");
                    }
                    System.exit(0);
                }
                
                //check if values contained in foreign key field are also included within primary key field:

                int c = 0;
                for (String v : r.getEndC().getValues()) {                    
                    if (!r.getStartC().getValues().contains(v)) {
                        if(c==0)
                        {
                            System.out.append("Warning: Entry '" + v + "' in table '"+r.getEndT().getName()+"', column '"+r.getEndC().getName()+"' was declared as foreign key,"
                               + " but was not found in primary field '" + r.getStartC().getName() + "' in table '"+ r.getStartT().getName()+ "'."
                                + "\nThis may cause conflict with your database engine.\n");
                        }
                       c++;
                    }
                }

                if(c>1)
                    System.out.append("(" +(c-1) +" more)");

            }
        }
        if ((Mode == Mode.COMPILE) || (Mode == Mode.DECOMPILE)) {
            performSteps6to9();
        } else {
            System.out.append("File checking complete. No errors found.\n");
            System.exit(0);
        }
    }

    public static void performSteps6to9() {
        /*
         * This steps include the creation of the Sql file.
         * It takes the classes which were created from the Easy-sql file and translates them into native sql.         *
         */

        if (Mode == Mode.DECOMPILE) {

            String nl = System.getProperty("line.separator");

            String output = "";

            output += "database";
            output += nl + "{";
            output += nl + "\tdatabase.entries";
            output += nl + "\t{";

            for (Table t : tables) {
                output += nl + "\t\t" + t.getName();
                output += nl + "\t\t{";

                for (Column c : t.getColumns()) {
                    output += nl + "\t\t\t" + c.getName();
                    output += nl + "\t\t\t{";
                    output += nl + "\t\t\t\t";
                    for (int i = 0; i < c.getValues().size(); i++) {
                        output += c.getValues().get(i);
                        if (i < (c.getValues().size() - 1)) {
                            output += ",";
                        }
                    }
                    output += nl + "\t\t\t}";
                }

                output += nl + "\t\t}";
            }

            output += nl + "\t}";
            if (relations.size() > 0) {
                output += nl + "\tdatabase.relations";
                output += nl + "\t{";
                for (Relation r : relations) {
                    output += nl + "\t\t";
                    output += r.getStartT().getName() + "." + r.getStartC().getName() + " -> " + r.getEndT().getName() + "." + r.getEndC().getName();

                    if((r.getUpdateOperation()!=null)||(r.getDeleteOperation()!=null))
                    {
                        output += nl + "\t\t{";
                        
                        if(r.getUpdateOperation()!=null)
                        {
                            output += nl + "\t\t\tupdate";
                            output += nl + "\t\t\t{";
                                output += nl + "\t\t\t\t";
                                if (r.getUpdateOperation() == Relation.Operations.CASCADE) {
                                    output += "cascade";
                                }
                                if (r.getUpdateOperation() == Relation.Operations.NO_ACTION) {
                                    output += "no action";
                                }
                                if (r.getUpdateOperation() == Relation.Operations.RESTRICT) {
                                    output += "restrict";
                                }
                                if (r.getUpdateOperation() == Relation.Operations.SET_NULL) {
                                    output += "set null";
                                }
                            output += nl + "\t\t\t}";
                        }
                        if(r.getDeleteOperation()!=null)
                        {
                            output += nl + "\t\t\tdelete";
                            output += nl + "\t\t\t{";
                                output += nl + "\t\t\t\t";
                                if (r.getDeleteOperation() == Relation.Operations.CASCADE) {
                                    output += "cascade";
                                }
                                if (r.getDeleteOperation() == Relation.Operations.NO_ACTION) {
                                    output += "no action";
                                }
                                if (r.getDeleteOperation() == Relation.Operations.RESTRICT) {
                                    output += "restrict";
                                }
                                if (r.getDeleteOperation() == Relation.Operations.SET_NULL) {
                                    output += "set null";
                                }
                            output += nl + "\t\t\t}";
                        }
                        output += nl + "\t\t}";
                    }
                }
                output += nl + "\t}";
            }
            output += nl + "}";


            BufferedWriter out = null;
            //remove comment block for testing
            //System.out.append("s");
            try {
                out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile)));
                out.write(output);
                out.close();
            } catch (FileNotFoundException err) {
                System.out.println("Error in commandline: Output file does not exist.\n");
            } catch (IOException err) {
                System.out.println("\nCritical error: program returned an undefined issue.\n");
            }
        } else {
            /*
             * Changes:
             * + Added possibility to create a complete new database
             * + overwrites tables now
             * + datatypes Time, Tinyint (Boolean), Text
             * + increase length depending on datatype
             * + added on delete/update
             * + added auto_increment option
             */
            // <editor-fold  defaultstate="collapsed" desc="in code comments">
            String sinstruction = "-- SQL converted Datapace file\n-- Created with Datapace compiler\n--";
            String ssettings = "\n\nSET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n\nSET FOREIGN_KEY_CHECKS = 0;\n\n";
            String com1 = "--\n-- Database\n--\n";
            String com2 = "\n\n-- ------------\n\n--\n-- Tables\n--\n\n";
            String com3 = "\n\n-- ------------\n\n--\n-- Values\n--\n\n";
            String com4 = "\n\n-- ------------\n\n--\n-- Relations\n--\n\nSET FOREIGN_KEY_CHECKS = 1;\n\n";
            String com5 = "\n\n-- End of file";
            //</editor-fold>

            String creator = "";
            String table = "";
            String articles = "";

            String stacker = "";
            String columnstacker = "";
            String stypea = "";
            String stypeb = "";
            boolean lengthneeded = true;
            List<String> goods = new ArrayList();
            String sprimary = "";
            String sforeign = "";

            String s = "";

            //just counters for tables, columns, values
            Integer it = 0;
            Integer ic = 0;
            Integer iv = 0;

            // <editor-fold  defaultstate="collapsed" desc="trawl tables">
            for (Table t : tables) {
                it++;
                table = "DROP TABLE IF EXISTS `" + t.getName() + "`;\nCREATE TABLE IF NOT EXISTS `" + t.getName() + "` (\n";
                //This creates the indexes for the filling with the values
                goods.clear();
                for (String g : t.getColumns().get(0).getValues()) {
                    goods.add("");
                }
                //resetting single variables
                columnstacker = "";
                sprimary = "";
                sforeign = "";
                // <editor-fold  defaultstate="collapsed" desc="trawl columns">
                for (Column c : t.getColumns()) {
                    ic++;
                    //Auto increment
                    String auto_incr = "";
                    stypeb = "";
                    
                    lengthneeded = true;
                    columnstacker = columnstacker + ", `" + c.getName() + "`";
                    String a = c.getValues().get(0);

                    if (c.getIsPrimaryKey()) {
                        sprimary = c.getName();
                    }
                    if (c.getIsForeignKey()) {
                        sforeign = sforeign + ",\n" + "KEY `" + c.getName().toString() + "` (`" + c.getName().toString() + "`)";
                    }

                    // checking for datatypes
                    int max = a.length();
                    int i = 0;
                    if(c.datatypeisdefined())
                    {
                    switch(c.getDatatype())
                        {
                            case INTEGER:
                                stypea = "NOT NULL";
                                break;
                            case VARCHAR:
                                stypea = "COLLATE latin1_german1_ci NOT NULL";
                                stypeb = "'";
                                break;
                            case TEXT:
                                stypea = "NOT NULL";
                                lengthneeded = false;
                                stypeb = "'";
                                break;
                            case DATE:
                                lengthneeded = false;
                                stypeb = "'";
                                break;
                            case TIME:
                                lengthneeded = false;
                                stypeb = "'";
                                break;
                            case DOUBLE:
                                stypea = "NOT NULL";
                                lengthneeded = false;
                                break;
                            case TINYINT:
                                break;
                            case UNKNOWN:
                                stypea = c.getDatatypeExtension(c.dtype);
                                stypeb = c.getDatatypeSign(c.dtype);
                                if(c.getDatatypeLength(c.dtype).equals("0"))
                                {
                                    lengthneeded = false;
                                }else if(c.getDatatypeLength(c.dtype).equals("99"))
                                {
                                    lengthneeded = true;
                                }else{
                                    lengthneeded = true;
                                    c.setLength(Integer.parseInt(c.getDatatypeLength(c.dtype)));
                                }
                                break;
                            default:
                                break;
                        }
                    }else{
                        //Time
                    if (a.indexOf(":") == 2 && a.lastIndexOf(":") == 5) {
                        c.setDatatype(Column.Datatypes.TIME);
                        lengthneeded = false;
                        stypeb = "'";
                    }
                    //Datetime
                    if (a.indexOf("-") == 4 && a.lastIndexOf("-") == 7) {
                        c.setDatatype(Column.Datatypes.DATE);
                        lengthneeded = false;
                        stypeb = "'";
                    }
                    //Integer or Double
                    String x = a;
                    if (x.startsWith("-") || x.startsWith("+")) {
                        x = x.substring(1);
                    }
                    x = x.replace("0", "");
                    x = x.replace("1", "");
                    x = x.replace("2", "");
                    x = x.replace("3", "");
                    x = x.replace("4", "");
                    x = x.replace("5", "");
                    x = x.replace("6", "");
                    x = x.replace("7", "");
                    x = x.replace("8", "");
                    x = x.replace("9", "");
                    if (x.isEmpty()) {
                        c.setDatatype(Column.Datatypes.INTEGER);
                        stypea = "NOT NULL";
                    } else {
                        if (x.equals(".")) {
                            c.setDatatype(Column.Datatypes.DOUBLE);
                            stypea = "NOT NULL";
                            lengthneeded = false;
                        }
                    }

                    boolean tinyint = true;
                    boolean text = false;

                    for (String b : c.getValues()) {
                        i++;
                        if (b.length() > max) {
                            max = b.length();
                        }
                        if (!b.equals("0")) {
                            if (!b.equals("1")) {
                                tinyint = false;
                            }
                        }
                        if (b.equals("\r") || b.equals("\n")) {
                            text = true;
                        }
                    }
                    //tinyint (Boolean)
                    if (i > 2 && tinyint) {
                        c.setDatatype(Column.Datatypes.TINYINT);
                    }
                    //text
                    if (max > 100 || text) {
                        c.setDatatype(Column.Datatypes.TEXT);
                        stypea = "NOT NULL";
                        lengthneeded = false;
                        stypeb = "'";
                    }
                    //Last chance: String
                    if (c.getDatatype() == null) {
                        c.setDatatype(Column.Datatypes.VARCHAR);
                        stypea = "COLLATE latin1_german1_ci NOT NULL";
                        stypeb = "'";
                    }
                    }
                    // <editor-fold  defaultstate="collapsed" desc="trawl values">
                    i = 0;
                    for (String b : c.getValues()) {
                        iv++;
                        goods.set(i, goods.get(i) + ", " + stypeb + b + stypeb);
                        i++;
                    }
                    //</editor-fold>

                    //round length
                    if(c.lengthisempty())
                    {
                    if (c.getDatatype().equals(Column.Datatypes.INTEGER)) {
                        max = max + 2;
                    }
                    if (c.getDatatype().equals(Column.Datatypes.VARCHAR)) {
                        Double dbl = max * 1.5;
                        max = dbl.intValue();
                    }
                    }else{
                        max = c.getLength();
                    }
                    c.setLength(max);
                    if (c.getIsAI() && c.getDatatype().equals(Column.Datatypes.INTEGER)) {
                        auto_incr = "AUTO_INCREMENT";
                    }
                    if(c.getDatatype().equals(Column.Datatypes.SERIAL) || c.dtype.equals("SERIAL"))
                    {
                        sforeign = sforeign + ",\n" + "UNIQUE KEY `" + c.getName().toString() + "` (`" + c.getName().toString() + "`)";
                    }
                    if(c.dtype.equals("SERIAL"))
                    {
                        c.dtype = "BIGINT";
                    }
                    if (lengthneeded) {
                        if(c.getDatatype().equals(Column.Datatypes.UNKNOWN))
                        {
                        table = table + "`" + c.getName() + "` " + c.dtype.toUpperCase() + "(" + c.getLength() + ") " + stypea + " " + auto_incr + ",\n";
                        }else{
                        table = table + "`" + c.getName() + "` " + c.getDatatype() + "(" + c.getLength() + ") " + stypea + " " + auto_incr + ",\n";
                        }
                    } else {
                        if(c.getDatatype().equals(Column.Datatypes.UNKNOWN))
                        {
                        table = table + "`" + c.getName() + "` " + c.dtype.toUpperCase() + " " + stypea + " " + auto_incr + ",\n";
                        }else{
                        table = table + "`" + c.getName() + "` " + c.getDatatype() + " " + stypea + " " + auto_incr + ",\n";
                        }
                    }

                }
                //</editor-fold>
                if (!sprimary.equals("")) {
                    table = table + "PRIMARY KEY (`" + sprimary + "`)";
                    table = table + sforeign;
                } else {
                    table = table + sforeign.substring(2);
                }
                table = table + "\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;\n\n";
                creator = creator + table;
                stacker = "INSERT INTO `" + t.getName() + "` (" + columnstacker.substring(2) + ") VALUES";
                for (String g : goods) {
                    stacker = stacker + "\n(" + g.substring(2) + "),";
                }
                stacker = stacker.substring(0, stacker.length() - 1) + ";\n\n";
                articles = articles + stacker;
            }
            //</editor-fold>

            // <editor-fold  defaultstate="collapsed" desc="Add relations">
            String ra = "";
            String rb = "";
            Integer i = 1;
            for (Relation r : relations) {
                ra = "ALTER TABLE `" + r.getEndT().getName() + "`\nADD CONSTRAINT `" + r.getEndT().getName() + "_ibfk_1" + i.toString() + "` FOREIGN KEY (`" + r.getEndC().getName() + "`) REFERENCES `" + r.getStartT().getName() + "` (`" + r.getStartC().getName() + "`)";

                if (r.getUpdateOperation() != null) {
                    ra += " ON UPDATE " + r.getUpdateOperation().name();
                }
                if (r.getDeleteOperation() != null) {
                    ra += " ON DELETE " + r.getDeleteOperation().name();
                }
                ra += ";";

                rb = rb + ra + "\n\n";
                i++;
            }
            //</editor-fold>

            // <editor-fold  defaultstate="collapsed" desc="merge">
            if (dbName.length() > 0) {
                String db = "DROP DATABASE IF EXISTS " + dbName + ";\nCREATE DATABASE `" + dbName + "` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;\nUSE `" + dbName + "`;";
                s = sinstruction + ssettings + com1 + db + com2 + creator + com3 + articles + com4 + rb + com5;

            } else {
                s = sinstruction + ssettings + com1 + com2 + creator + com3 + articles + com4 + rb + com5;
            }

            //</editor-fold>


            // <editor-fold  defaultstate="collapsed" desc="Parse file">
            BufferedWriter out = null;

            //s = sinstruction + ssettings + com1 + com2 + creator + com3 + articles + com4 + rb + com5;
            //remove comment block for testing
            //System.out.append(s);
            try {
                out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile)));
                out.write(s);
                out.close();
                //System.out.append(it.toString() + " Tables\n" + ic.toString() + " Columns\n" + iv.toString() + " Entries\nsuccessfully compiled.\n");
            } catch (FileNotFoundException err) {
                System.out.println("Error in commandline: Output file does not exist.\n");
            } catch (IOException err) {
                System.out.println("\nCritical error: program returned an undefined issue.\n");
            }
            //</editor-fold>


        }
    }
}
